﻿/****** Object:  View [dbo].[vw_medianamik_NodePropertyValueGeneration]    Script Date: 02/02/2009 14:30:58 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vw_medianamik_NodePropertyValueGeneration]'))
BEGIN
	DECLARE @q NVARCHAR(MAX)
    SELECT @q = 'Create VIEW [dbo].[vw_medianamik_NodePropertyValueGeneration]
	AS
	select npv1.NodePropertyValueId, npv1.nodeid, npv1.Propertyid, npv1.Value, npv1.Revision , npv1.cultureName,  CASE WHEN npv1.Revision IS NULL THEN - 1 ELSE Count(npv2.Revision)-1 END AS Generation
	from medianamik_nodepropertyvalue npv1 
	Left Outer JOIN dbo.medianamik_NodePropertyValue AS npv2 ON npv1.PropertyId = npv2.PropertyId AND npv1.NodeId = npv2.NodeId AND npv1.CultureName = npv2.CultureName
	WHERE ISNULL(npv2.Revision,dbo.fn_medianamik_MaxRevision()) >= ISNULL(npv1.revision,dbo.fn_medianamik_MaxRevision())
	Group by npv1.NodePropertyValueId, npv1.nodeid, npv1.Propertyid, npv1.Value, npv1.revision, npv1.cultureName'
	EXEC sp_executesql @q
END

--Création de la vue [dbo].[vw_medianamik_DuplicateProperty] qui est manquante dans le script initial
IF NOT EXISTS (SELECT NULL FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vw_medianamik_DuplicateProperty]'))
BEGIN
	DECLARE @q2 NVARCHAR(MAX)
	SELECT @q2 = 'CREATE VIEW [dbo].[vw_medianamik_DuplicateProperty]
	AS
	SELECT     NodeId,PropertyId, Value,   Revision, CultureName, COUNT(*) AS DuplicateTime
	FROM         dbo.medianamik_NodePropertyValue
	GROUP BY NodeId,PropertyId, Value,  Revision, CultureName
	HAVING      (COUNT(*) > 1)'
	EXEC sp_executesql @q2
END

IF EXISTS(select * from sys.objects where object_id = OBJECT_ID(N'[dbo].[DF_medianamik_MedianamikControl_IsTree]'))
BEGIN
	ALTER TABLE dbo.medianamik_MedianamikControl
    DROP CONSTRAINT DF_medianamik_MedianamikControl_IsTree
END


IF EXISTS(
Select * FROM Information_Schema.COLUMNS
where TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'medianamik_MedianamikControl'
AND Column_Name = 'IsTree'
)
BEGIN
	ALTER TABLE dbo.medianamik_MedianamikControl DROP COLUMN IsTree
END